package objetos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Forca {

	private String URL = "jdbc:mysql://127.0.0.1:3306/test";
	private String USE = "root";
	private String PASS = "";

	public boolean inserir(ForcaBean forca) {
		String driver = "com.mysql.jdbc.Driver";
		Connection con;
		boolean res = false;
		boolean verificaPalavra = verificaPalavraExiste(forca.getPalavra());

		if (verificaPalavra) {
			// palavra ja existe
			res = false;
		} else {

			try {
				Class.forName(driver);
				con = DriverManager.getConnection(URL, USE, PASS);
				Statement stmt = con.createStatement();

				String sql = "INSERT INTO FORCA (PALAVRA, NIVEL, DICA1, DICA2) VALUES ('"
						+ forca.getPalavra()
						+ "','"
						+ forca.getNivel()
						+ "','"
						+ forca.getDica1() + "','" + forca.getDica2() + "');";
				// con.commit();
				stmt.executeUpdate(sql);
				res = true;
				con.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();

				res = false;
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();

				res = false;
			}
		}
		return res;

	}

	public boolean verificaPalavraExiste(String palavra) {
		String driver = "com.mysql.jdbc.Driver";
		Connection con;
		boolean res = false;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			Statement stmt = con.createStatement();
			Statement st = null;
			ResultSet rs = null;
			st = con.createStatement();
			// seleciona id
			String sql = "SELECT IDFORCA AS IDFORCA FROM FORCA WHERE PALAVRA = '"
					+ palavra + "'";

			// Autenticar
			st = con.createStatement();
			rs = st.executeQuery(sql);

			if (rs.next()) {
				res = true;
			}

			sql = "";
			// con.commit();
			stmt.executeUpdate(sql);

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

			res = false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

			res = false;
		}
		return res;
	}

	public boolean atualizar(ForcaBean forca) {
		String driver = "com.mysql.jdbc.Driver";
		Connection con;
		boolean res = false;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			Statement stmt = con.createStatement();
			Statement st = null;
			ResultSet rs = null;
			st = con.createStatement();
			// seleciona id
			String sql = "SELECT IDFORCA AS IDFORCA FROM FORCA WHERE PALAVRA = '"
					+ forca.getPalavra() + "'";

			// Autenticar
			st = con.createStatement();
			rs = st.executeQuery(sql);
			int idForca = 0;
			if (rs.next()) {
				idForca = rs.getInt("IDFORCA");
			}

			sql = "UPDATE FORCA SET PALAVRA = '" + forca.getPalavra()
					+ "' WHERE IDFORCA = " + idForca;
			stmt.executeUpdate(sql);
			sql = "UPDATE FORCA SET NIVEL = '" + forca.getNivel()
					+ "' WHERE IDFORCA = " + idForca;
			stmt.executeUpdate(sql);
			sql = "UPDATE FORCA SET DICA1 = '" + forca.getDica1()
					+ "' WHERE IDFORCA = " + idForca;
			stmt.executeUpdate(sql);
			sql = "UPDATE FORCA SET DICA2 = '" + forca.getDica2()
					+ "' WHERE IDFORCA = " + idForca;
			stmt.executeUpdate(sql);
			res = true;
			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

			res = false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

			res = false;
		}
		return res;

	}

	public boolean excluir(ForcaBean forca) {
		String driver = "com.mysql.jdbc.Driver";
		Connection con;
		boolean res = false;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			Statement stmt = con.createStatement();
			Statement st = null;
			ResultSet rs = null;
			st = con.createStatement();
			// seleciona id
			String sql = "SELECT IDFORCA AS IDFORCA FROM FORCA WHERE PALAVRA = '"
					+ forca.getPalavra() + "'";

			// Autenticar
			st = con.createStatement();
			rs = st.executeQuery(sql);
			int idForca = 0;
			if (rs.next()) {
				idForca = rs.getInt("IDFORCA");
			}

			sql = "DELETE FROM FORCA WHERE IDFORCA = " + idForca;
			stmt.executeUpdate(sql);
			res = true;
			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

			res = false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

			res = false;
		}
		return res;

	}

	public ForcaBean getPalavraByNivel(String nivel) {
		String driver = "com.mysql.jdbc.Driver";
		Connection con;
		ForcaBean palavra = new ForcaBean();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			Statement stmt = con.createStatement();
			Statement st = null;
			ResultSet rs = null;
			st = con.createStatement();
			// seleciona id
			String sql = "SELECT IDFORCA AS IDFORCA, PALAVRA AS PALAVRA, NIVEL AS NIVEL, DICA1 AS DICA1, DICA2 AS DICA2 FROM FORCA WHERE NIVEL = '"
					+ nivel + "' ORDER BY RAND() LIMIT 1";

			// Autenticar
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				palavra.setIdForca(rs.getInt("IDFORCA"));
				palavra.setDica1(rs.getString("DICA1"));
				palavra.setDica2(rs.getString("DICA2"));
				palavra.setNivel(rs.getString("NIVEL"));
				palavra.setPalavra(rs.getString("PALAVRA"));
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return palavra;
	}

}
